package cn.net.autocode.platform.excel.service;

import cn.hutool.core.util.StrUtil;
import cn.net.autocode.platform.exception.PlatformQueryException;
import cn.net.autocode.platform.providers.service.SaveServ;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import cn.net.autocode.dbManager.Dao;
import cn.net.autocode.platform.excel.handle.ExcelHandle;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import java.util.*;


@Service("platformExcelServ")
public class ExcelServ {

    private final Dao dao;

    private final Dao dictDao;

    private final SaveServ saveServ;

    public ExcelServ(Dao dao, @Qualifier("dictDao") Dao dictDao, SaveServ saveServ) {
        this.dao = dao;
        this.dictDao = dictDao;
        this.saveServ = saveServ;
    }

    public JSONObject excelReadTemplate(String templateCode,JSONArray excelDatas){
        JSONObject template = dao.queryJSON("select * from sys_excel_template where code=?",templateCode);
        JSONArray columns = dao.queryJSONArray("select * from sys_excel_template_field where template_code=? order by sort_no",templateCode);
        if(template.isEmpty() || columns.isEmpty()){
            throw new RuntimeException(templateCode+"没有在excel导入设置中维护");
        }
        int titleNo = template.getShort("titleNo");
        if(titleNo<1){
            titleNo=1;
        }
        template.remove("id");
        template.remove("sortNo");


        //表头对应的列,如何处理多一列、少一列的问题
        //多一列禁止导入
        //少一列，需要验证是否为必填项，如果为必填项则报出异常，如果不为必填项，可不做处理
        Map<String,JSONObject> lsMap = new HashMap<>();
        for(int index=0;index<columns.size();index++){
            JSONObject cell = columns.getJSONObject(index);
            cell.remove("id");
            cell.remove("sortNo");
            cell.put("columnIndex",-1);
            lsMap.put(cell.getString("label"),cell);

        }

        JSONArray titleRow = excelDatas.getJSONArray(titleNo-1);
        for(int i=0;i<titleRow.size();i++){
            JSONObject cell = titleRow.getJSONObject(i);
            String label = cell.getString("value");
            if (label==null||label.trim().isBlank()){
                continue;
            }
            int columnIndex = cell.getIntValue("columnIndex");
            if(!lsMap.containsKey(label)){
                throw new RuntimeException("【"+label+"】没有在Excel导入设置中配置，请检测模板或Excel导入设置是否正确！");
            }else{
                lsMap.get(label).put("columnIndex",columnIndex);
                lsMap.remove(label);
            }
        }

        //验证是否缺少列
        for(Map.Entry<String,JSONObject> entry:lsMap.entrySet()){
            String isRequired = entry.getValue().getString("isRequired");
            if("Y".equals(isRequired)){
                throw new RuntimeException("导入文件中缺少【"+entry.getValue().getString("label")+"】信息，请检查使用的模板是否正确！");
            }
        }

        template.put("titleRowIndex",titleNo);
        template.put("titleCells",columns);
        return template;
    }

    /**
     * 将excel转化为可处理数据
     */
    public JSONArray parseExcelData(JSONObject template,JSONArray excelDatas){
        int titleRowIndex = template.getIntValue("titleRowIndex");
        String mainTable = template.getString("mainTable");

        JSONArray titleCells = template.getJSONArray("titleCells");
        Map<Integer,JSONObject> titleColumnIndexs = new HashMap<>();
        Set<String> mainKeys = new HashSet<>();
        Map<String,JSONObject> dictMap = new HashMap<>();

        for(int i=0;i<titleCells.size();i++){
            JSONObject title = titleCells.getJSONObject(i);
            titleColumnIndexs.put(title.getIntValue("columnIndex"),title);
            String cellTable= title.getString("tableName");
            if(mainTable.equals(cellTable)){
                mainKeys.add(StrUtil.toCamelCase(title.getString("columnName")));
            }
            //设置数据字典
            String dataType = title.getString("dataType");
            if ("dict".equals(dataType)){
                String dictCode = title.getString("dictCode");
                if(dictCode==null || dictCode.isBlank()){
                    throw new RuntimeException("【"+title.getString("label")+"】列没有设置字典代码！");
                }
                dictCode = dictCode.trim();
                JSONObject dict = dao.queryJsonVT("select label value,code text from sys_dict_item where dict_code=?",dictCode);
                if(dict==null || dict.isEmpty()){
                    throw new RuntimeException("字典代码【"+dictCode+"】对应的字典值没有维护！");
                }
                dictMap.put(dictCode,dict);
            }
        }

        JSONArray dataArray1 = new JSONArray();
        //1、转化为JSONObject对象
        for(int i=titleRowIndex;i<excelDatas.size();i++){
            JSONArray row = excelDatas.getJSONArray(i);
            //主表数据
            JSONObject mainTableJson = new JSONObject();
            //去除空行数据
            for(int j=0;j<row.size();j++){
                JSONObject cell = row.getJSONObject(j);
                int columnIndex = cell.getIntValue("columnIndex");
                JSONObject titleCell = titleColumnIndexs.get(columnIndex);
                if(titleCell!=null){
                    //数据处理
                    Object cellValue = ExcelHandle.valueTransform(cell,titleCell,dictMap);
                    if (cellValue==null){
                        continue;
                    }else if(cellValue instanceof String str){
                        if (str.isBlank()){
                            continue;
                        }
                    }

                    String code = StrUtil.toCamelCase(titleCell.getString("columnName"));
                    String cellTable= titleCell.getString("tableName");
                    if(mainTable.equals(cellTable)){
                        mainTableJson.put(code,cellValue);
                    }else{
                        if (mainTableJson.containsKey(cellTable)){
                            mainTableJson.getJSONArray(cellTable).getJSONObject(0).put(code,cellValue);
                        }else{
                            JSONObject childrenTableJson = new JSONObject();
                            childrenTableJson.put(code,cellValue);
                            JSONArray childrens = new JSONArray();
                            childrens.add(childrenTableJson);
                            mainTableJson.put(cellTable,childrens);
                        }
                    }
                }
            }

            if (!mainTableJson.isEmpty()){
                dataArray1.add(mainTableJson);
            }
        }

        /*
         * 第二步：主表数据去重
         */
        JSONArray dataArray2 = new JSONArray();
        JSONObject preJson = null;
        for(int i=0;i<dataArray1.size();i++){
            JSONObject curJson = dataArray1.getJSONObject(i);
            if (preJson==null){
                preJson = curJson;
                dataArray2.add(preJson);
            }else{
                //判断主表数据是否为空
                boolean curIsEmpty = true;
                for(String key:mainKeys){
                    if (curJson.containsKey(key)){
                        curIsEmpty = false;
                        break;
                    }
                }

                boolean isSame = true;
                if(!curIsEmpty){
                    for(String key:mainKeys){
                        if (preJson.containsKey(key)||curJson.containsKey(key)){
                            Object preObject = preJson.get(key);
                            Object curObject = curJson.get(key);
                            if (preObject==null || curObject==null){
                                isSame = false;
                                break;
                            }else if(preObject instanceof JSONArray){
                                break;
                            }else if(preObject instanceof String pre && curObject instanceof String cur){
                                if(!pre.equals(cur)){
                                    isSame = false;
                                    break;
                                }
                            }else if(preObject instanceof Date pre && curObject instanceof Date cur){
                                if(pre.compareTo(cur)!=0){
                                    isSame = false;
                                    break;
                                }
                            }else if(preObject instanceof Double pre && curObject instanceof Double cur){
                                if(Double.compare(pre, cur)!=0){
                                    isSame = false;
                                    break;
                                }
                            }
                        }
                    }
                }

                if(isSame){
                    Set<String> keys=curJson.keySet();
                    for(String key:keys){
                        if(!mainKeys.contains(key)){
                            JSONArray curValue =  curJson.getJSONArray(key);
                            if (preJson.containsKey(key)){
                                preJson.getJSONArray(key).addAll(curValue);
                            }else{
                                preJson.put(key,curValue);
                            }
                        }
                    }
                }else{
                    preJson = curJson;
                    dataArray2.add(preJson);
                }
            }
        }


        //必填项校验
        for (int i=0;i<titleCells.size();i++){
            JSONObject titleCell = titleCells.getJSONObject(i);
            if ("Y".equals(titleCell.getString("isRequired"))){
                String code = StrUtil.toCamelCase(titleCell.getString("columnName"));
                String cellTable= titleCell.getString("tableName");
                for(int j=0;j<dataArray2.size();j++){
                    JSONObject data = dataArray2.getJSONObject(j);
                    if (mainTable.equals(cellTable)){
                        Object value = data.get(code);
                        if(value==null || value.toString().isBlank()){
                            throw new RuntimeException(titleCell.getString("label")+"数据不能为空");
                        }
                    }else{
                        if(!data.containsKey(cellTable)){
                            throw new RuntimeException(titleCell.getString("label")+"数据不能为空");
                        }else{
                            JSONArray children = data.getJSONArray(cellTable);
                            for(int k=0;k<children.size();k++){
                                Object childrenValue = children.getJSONObject(k).get(code);
                                if(childrenValue==null || childrenValue.toString().isBlank()){
                                    throw new RuntimeException(titleCell.getString("label")+"数据不能为空");
                                }
                            }
                        }
                    }
                }
            }
        }

        return dataArray2;
    }



    //处理主键信息，并进行信息导入
    public void dataSave(JSONObject template,JSONArray tableData) throws PlatformQueryException {
        String mainTable = template.getString("mainTable");

        /*
         * 第一步：保存主表数据
         */
        JSONObject table = saveServ.tableMsgByTableName(mainTable);
        JSONArray columnsMsg = saveServ.columnsMsg(table.getString("id"));
        JSONArray rules = saveServ.rules(table.getString("id"));
        for(int i=0;i<tableData.size();i++){
            JSONObject mainJson = tableData.getJSONObject(i);
            saveServ.save(mainJson,mainTable,columnsMsg,rules);
        }

        /*
          第一步：保存子表数据
         */
        Set<String> refTables = new HashSet<>();
        JSONArray titleCells = template.getJSONArray("titleCells");

        for(int i=0;i<titleCells.size();i++){
            JSONObject titleCell = titleCells.getJSONObject(i);
            if(!mainTable.equals(titleCell.getString("tableName"))){
                refTables.add(titleCell.getString("tableName"));
            }
        }

        for(String rt:refTables){
            //获取关联字段
            Map<String, Object> refColumn = dictDao.queryMapVT("select ac_table_ref.pk_field value,ac_table_ref.fk_field text from ac_table_ref,ac_table where ac_table.id=ac_table_ref.table_id and ac_table.table_name=? and fk_table=? order by fk_table",mainTable,rt);
            if (refColumn.isEmpty()){
                continue;
            }
            JSONObject refTable = saveServ.tableMsgByTableName(rt);
            JSONArray refColumnsMsg = saveServ.columnsMsg(refTable.getString("id"));
            JSONArray refRules = saveServ.rules(refTable.getString("id"));
            for(int i=0;i<tableData.size();i++){
                JSONObject mainJson = tableData.getJSONObject(i);
                JSONArray childrenData = mainJson.getJSONArray(rt);
                for(int j=0;j<childrenData.size();j++){
                    JSONObject childrenJson = childrenData.getJSONObject(j);
                    for(Map.Entry<String,Object> entry:refColumn.entrySet()){
                        if(mainJson.containsKey(StrUtil.toCamelCase(entry.getKey()))){
                            childrenJson.put(StrUtil.toCamelCase(entry.getValue().toString()),mainJson.get(StrUtil.toCamelCase(entry.getKey())));
                        }else{
                            throw new RuntimeException("导入数据没有设置子表"+rt+"与主表"+mainTable+"的关联字段"+entry.getKey()+"的值");
                        }
                    }
                    saveServ.save(childrenJson,rt,refColumnsMsg,refRules);
                }
            }
        }
    }

    public JSONObject excelTemplate(String templateCode){

        JSONObject template = dao.queryJSON("select * from sys_excel_template where code=?",templateCode);
        JSONArray columns = dao.queryJSONArray("select * from sys_excel_template_field where template_code=? order by sort_no",templateCode);
        if(template.isEmpty() || columns.isEmpty()){
            throw new RuntimeException(templateCode+"没有在excel导入设置中维护");
        }

        int titleNo = template.getShort("titleNo");
        if(titleNo<1){
            titleNo=1;
        }

        template.put("titleNo",titleNo);

        for(int i=0;i<columns.size();i++){
            JSONObject cell = columns.getJSONObject(i);
            cell.put("columnIndex",i);
            if(StrUtil.isNotBlank(cell.getString("columnAlias"))){
                cell.put("code",cell.getString("columnAlias"));
            }else{
                cell.put("code",StrUtil.toCamelCase(cell.getString("columnName")));
            }
        }

        template.put("titleCells",columns);

        return template;
    }
}
